package org.prs.persistence;

import com.prs.util.PrsException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.prs.model.Child;
import org.prs.model.Photo;
import org.prs.persistence.connector.MySQLDatabaseConnector;

public class ChildDatabasePersitenceManager {

    private Connection conn;
    private ResultSet result;

    public boolean insertChild(Child child) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertChild = null;
        try {
            insertChild = conn.prepareStatement("insert into child set "
                    + " firstname=?, lastname=?, dateofbirth=STR_TO_DATE(?,'%d/%m/%Y'), creatorid=?,"
                    + " address=?, childcode=?, district=?, gender=?, "
                    + "middlename=?, phonenumber=?, residence=?, schoolstageid=?");

            insertChild.setString(1, child.getFirstName());
            insertChild.setString(2, child.getLastName());
            insertChild.setString(3, new SimpleDateFormat("dd/MM/yyyy").format(child.getDateOfBirth()));
            insertChild.setInt(4, child.getCreatorId());
            insertChild.setString(5, child.getAddress());
            insertChild.setString(6, child.getChildCode());
            insertChild.setString(7, child.getDistrict());
            insertChild.setString(8, child.getGender());
            insertChild.setString(9, child.getMiddleName());
            insertChild.setString(10, child.getPhoneNumber());
            insertChild.setString(11, child.getResidence());
            insertChild.setInt(12, child.getSchoolStageId());
            insertChild.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

    public boolean insertPhoto(Photo photo) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertChildPhoto = null;
        try {
            insertChildPhoto = conn.prepareStatement("insert into photo set "
                    + " datecreated=STR_TO_DATE(?,'%d/%m/%Y'), name=?, path=?, childid=?, "
                    + "creatorid=?");

            insertChildPhoto.setString(1, photo.getDateCreated());
            insertChildPhoto.setString(2, photo.getName());
            insertChildPhoto.setString(3, photo.getPath());
            insertChildPhoto.setInt(4, photo.getChildId());
            insertChildPhoto.setInt(5, photo.getCreatorId());
            insertChildPhoto.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

    public boolean updateChild(Child child) {
        boolean updated = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement updateChild = null;
        try {
            updateChild = conn.prepareStatement("update child set "
                    + " firstname=?, lastname=?, dateofbirth=STR_TO_DATE(?,'%d/%m/%Y'), creatorid=?, "
                    + " address=?, childcode=?, district=?, gender=?, "
                    + "middlename=?, phonenumber=?, residence=?, schoolstageid=? where childid=?");

            updateChild.setString(1, child.getFirstName());
            updateChild.setString(2, child.getLastName());
            updateChild.setString(3, new SimpleDateFormat("dd/MM/yyyy").format(child.getDateOfBirth()));
            updateChild.setInt(4, child.getCreatorId());
            updateChild.setString(5, child.getAddress());
            updateChild.setString(6, child.getChildCode());
            updateChild.setString(7, child.getDistrict());
            updateChild.setString(8, child.getGender());
            updateChild.setString(9, child.getMiddleName());
            updateChild.setString(10, child.getPhoneNumber());
            updateChild.setString(11, child.getResidence());
            updateChild.setInt(12, child.getSchoolStageId());
            updateChild.setInt(13, child.getChildId());
            updateChild.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            updated = false;
        }
        return updated;
    }

    public Child getChildById(int id) {
        Child child = new Child();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectChildById = null;
        try {
            selectChildById = conn.prepareStatement("select * from child where childid=?");
            selectChildById.setInt(1, id);
            result = selectChildById.executeQuery();
            while (result.next()) {
                child = getChild(result);
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return child;
    }

    public List<Child> getAllChildren(int start, int size, String orderBy) {
        List<Child> children = new ArrayList<Child>();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectChildById = null;
        try {
            selectChildById = conn.prepareStatement("select * from child order by " + orderBy + " LIMIT ?,?");
            selectChildById.setInt(1, start);
            selectChildById.setInt(2, size);
            result = selectChildById.executeQuery();
            while (result.next()) {
                children.add(getChild(result));
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return children;
    }

    public int getChildrenCount() {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectChildrenCount = null;
        try {
            selectChildrenCount = conn.prepareStatement("select count(*) from child");
            result = selectChildrenCount.executeQuery();
            while (result.next()) {

                return result.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return 0;
    }

    private Child getChild(ResultSet result) {
        Child child = new Child();
        try {
            child = new Child();
            child.setChildId(result.getInt("childid"));
            child.setFirstName(result.getString("firstname"));
            child.setLastName(result.getString("lastname"));
            System.out.println(result.getString("dateofbirth"));
            try {
                child.setDateOfBirth(new SimpleDateFormat("yyyy-MM-dd").parse(result.getString("dateofbirth")));               
            } catch (ParseException e) {
                throw  new PrsException(e);
            }
            child.setAddress(result.getString("address"));
            child.setChildCode(result.getString("childcode"));
            child.setDistrict(result.getString("district"));
            child.setGender(result.getString("gender"));
            child.setMiddleName(result.getString("middlename"));
            child.setPhoneNumber(result.getString("phonenumber"));
            child.setResidence(result.getString("residence"));
            child.setSchoolStageId(result.getInt("schoolstageid"));
            child.setCreatorId(result.getInt("creatorid"));

        } catch (SQLException e) {
            throw new PrsException(e);
        }
        return child;
    }
}
